use master
go
create database bbs
on
(
name='bbs',
filename='D:\SQL\bbs.mdf'
)
log on
(
name='bbs_log',
filename='D:\SQL\bbs_log.ldf'
)
go
use bbs
go
create table bbsUsers
(
UID int primary key identity(1,1),
uName varchar(10) not null,
uSex varchar(2) not null check(uSex='男' or uSex='女'),
uAge int not null check(uAge>=15 and uAge<=40),
uPoint int not null check(uPoint>=0)
)
go
create table bbsTopic
(
tID int primary key identity(1,1),
tUID int,
tSID int,
tTitle varchar(100) not null,
tMsg text not null,
tTime datetime,
tCount int
)
go
create table bbsReply
(
rID int primary key identity(1,1),
rUID int constraint FK_bbsUsers_UID references bbsUsers(UID),
rTID int constraint FK_bbsTopic_tID references bbsTopic(tID),
rMsg text not null,
rTime datetime
)
go
create table bbsSection
(
sID int primary key identity(1,1),
sName varchar(10) not null,
sUid int,
)
go
alter table bbsTopic add constraint FK_bbsUsers1_UID foreign key(tUID) references bbsUsers(UID)
alter table bbsTopic add constraint FK_bbsSection_sID foreign key(tSID) references bbsSection(sID)
alter table bbsSection add constraint FK_bbsUsers2_UID foreign key(sUid) references bbsUsers(UID)
insert into bbsUsers(uName,uSex,uAge,uPoint) values('小雨点','女',20,0),('逍遥','男',18,4),('七年级生','男',19,2)
select uName,uPoint into bbsPoint from bbsUsers
insert into bbsSection(sName,sUid) values('技术交流',1),('读书世界',3),('生活百科',1),('八卦区',3)
insert into bbsTopic(tUID,tSID,tTitle,tMsg,tTime,tCount) values(2,4,'范跑跑','谁是范跑跑','2008-7-8',1),(3,1,'.NET','与JAVA的区别是什么呀？','2008-9-1',2),(1,3,'今年夏天最流行什么','有谁知道今年夏天最流行什么呀','2008-9-10',0)
insert into bbsReply(rMsg,rTime,rUID,rTID) values('666','2008-1-1',1,2),('666','2008-1-1',2,3),('666','2008-1-1',3,1)
select * from bbsUsers
select * from bbsTopic
select * from bbsReply
select * from bbsSection
--1.查询出每个版块的版主编号，版主姓名和版块名称
select UID,uName,sName from bbsUsers inner join bbsSection on bbsUsers.UID=bbsSection.sUid
--2.查询出主贴的发帖时间在2008-9-15以后的主贴的发帖人编号，发帖人姓名，帖子的标题，帖子的内容和发帖时间
select UID,uName,tTitle,tMsg,tTime from bbsUsers inner join bbsTopic on bbsUsers.UID=bbsTopic.tUID where tTime>'2008-9-15'
--3.查询出年龄在20以下的版主的编号，版主的名称和版块的名称
select UID,uName,sName from bbsUsers inner join bbsSection on bbsUsers.UID=bbsSection.sUid where uAge<20
--4.查询出回复数量最多的主贴的发帖人编号，发帖人姓名，主贴标题，主贴内容和回复数量
select UID,uName,tTitle,tMsg,tCount from bbsUsers inner join bbsTopic on bbsUsers.UID=bbsTopic.tUID where tCount=(select MAX(tCount) from bbsTopic)
--5.在主贴表中查询每个版块中每个用户的发帖总数
select sName,uName,count(tID) 发帖总数 from bbsTopic left join bbsSection on bbsTopic.tUID=bbsSection.sUid left join bbsUsers on bbsSection.sUid=bbsUsers.UID group by sName,uName
